﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_qcsicomill_ph
{
    public DAL_tbl_qcsicomill_ph()
    {

    }



    public List<tbl_qcsicomill_ph> select(String PH_ID)
    {
        List<tbl_qcsicomill_ph> list = new List<tbl_qcsicomill_ph>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select PH_ID,Work_Order_Number,DRUM_NUMBER,Solvant_PH,Solvant_Temp,Sample_0_hr_PH,Sample_0_hr_Temp,Sample_1_5_hr_PH,Sample_1_5_hr_Temp,Remark,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_qcsicomill_ph where PH_ID=@PH_ID";
        cmd.Parameters.AddWithValue("@PH_ID", PH_ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_qcsicomill_ph()
            {
                PH_ID = dr["PH_ID"].ToString(),
                Work_Order_Number = dr["Work_Order_Number"].ToString(),
                DRUM_NUMBER = dr["DRUM_NUMBER"].ToString(),
                Solvant_PH = dr["Solvant_PH"].ToString(),
                Solvant_Temp = dr["Solvant_Temp"].ToString(),
                Sample_0_hr_PH = dr["Sample_0_hr_PH"].ToString(),
                Sample_0_hr_Temp = dr["Sample_0_hr_Temp"].ToString(),
                Sample_1_5_hr_PH = dr["Sample_1_5_hr_PH"].ToString(),
                Sample_1_5_hr_Temp = dr["Sample_1_5_hr_Temp"].ToString(),
                Remark = dr["Remark"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_qcsicomill_ph> selectAll()
    {
        List<tbl_qcsicomill_ph> list = new List<tbl_qcsicomill_ph>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select PH_ID,Work_Order_Number,DRUM_NUMBER,Solvant_PH,Solvant_Temp,Sample_0_hr_PH,Sample_0_hr_Temp,Sample_1_5_hr_PH,Sample_1_5_hr_Temp,Remark,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_qcsicomill_ph Order By PH_ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_qcsicomill_ph()
            {
                PH_ID = dr["PH_ID"].ToString(),
                Work_Order_Number = dr["Work_Order_Number"].ToString(),
                DRUM_NUMBER = dr["DRUM_NUMBER"].ToString(),
                Solvant_PH = dr["Solvant_PH"].ToString(),
                Solvant_Temp = dr["Solvant_Temp"].ToString(),
                Sample_0_hr_PH = dr["Sample_0_hr_PH"].ToString(),
                Sample_0_hr_Temp = dr["Sample_0_hr_Temp"].ToString(),
                Sample_1_5_hr_PH = dr["Sample_1_5_hr_PH"].ToString(),
                Sample_1_5_hr_Temp = dr["Sample_1_5_hr_Temp"].ToString(),
                Remark = dr["Remark"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select PH_ID,Work_Order_Number,DRUM_NUMBER,Solvant_PH,Solvant_Temp,Sample_0_hr_PH,Sample_0_hr_Temp,Sample_1_5_hr_PH,Sample_1_5_hr_Temp,Remark,CASE WHEN IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',CREATED_BY,CREATED_DATE as CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_qcsicomill_ph Order By PH_ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }

    public bool insert(List<tbl_qcsicomill_ph> list)
    {
        bool ret = false;
        List<MySqlCommand> cmds = new List<MySqlCommand>();
        string sql = @"Insert into tbl_qcsicomill_ph(Work_Order_Number, DRUM_NUMBER, Solvant_PH, Solvant_Temp, Sample_0_hr_PH, Sample_0_hr_Temp, Sample_1_5_hr_PH, Sample_1_5_hr_Temp, Remark, IS_ACTIVE, CREATED_BY, CREATED_DATE)
                        values(@Work_Order_Number, @DRUM_NUMBER, @Solvant_PH, @Solvant_Temp, @Sample_0_hr_PH, @Sample_0_hr_Temp, @Sample_1_5_hr_PH, @Sample_1_5_hr_Temp, @Remark, @IS_ACTIVE, @CREATED_BY, @CREATED_DATE)";

        foreach (tbl_qcsicomill_ph row in list)
        {
            MySqlCommand cmd = new MySqlCommand(sql);

            cmd.Parameters.AddWithValue("@Work_Order_Number", row.Work_Order_Number);
            cmd.Parameters.AddWithValue("@DRUM_NUMBER", row.DRUM_NUMBER);
            
            cmd.Parameters.AddWithValue("@Solvant_PH", row.Solvant_PH);
            cmd.Parameters.AddWithValue("@Solvant_Temp", row.Solvant_Temp);
            cmd.Parameters.AddWithValue("@Sample_0_hr_PH", row.Sample_0_hr_PH);
            cmd.Parameters.AddWithValue("@Sample_0_hr_Temp", row.Sample_0_hr_Temp);
            cmd.Parameters.AddWithValue("@Sample_1_5_hr_PH", row.Sample_1_5_hr_PH);
            cmd.Parameters.AddWithValue("@Sample_1_5_hr_Temp", row.Sample_1_5_hr_Temp);
            
            cmd.Parameters.AddWithValue("@Remark", row.Remark);
            cmd.Parameters.AddWithValue("@IS_ACTIVE", true);
            cmd.Parameters.AddWithValue("@CREATED_BY", HttpContext.Current.User.Identity.Name);
            cmd.Parameters.AddWithValue("@CREATED_DATE", DateTime.Now);
            cmds.Add(cmd);
        }
        ret = DBUtil.ExecuteNonQuery(cmds);
        return ret;
    }

    public int insert(String Work_Order_Number, String DRUM_NUMBER, String Solvant_PH, String Solvant_Temp, String Sample_0_hr_PH, String Sample_0_hr_Temp, String Sample_1_5_hr_PH, String Sample_1_5_hr_Temp, String Remark, bool IS_ACTIVE, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_qcsicomill_ph(Work_Order_Number, DRUM_NUMBER, Solvant_PH, Solvant_Temp, Sample_0_hr_PH, Sample_0_hr_Temp, Sample_1_5_hr_PH, Sample_1_5_hr_Temp, Remark, IS_ACTIVE, CREATED_BY, CREATED_DATE)
                        values(@Work_Order_Number, @DRUM_NUMBER, @Solvant_PH, @Solvant_Temp, @Sample_0_hr_PH, @Sample_0_hr_Temp, @Sample_1_5_hr_PH, @Sample_1_5_hr_Temp, @Remark, @IS_ACTIVE, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@Work_Order_Number", Work_Order_Number);
        cmd.Parameters.AddWithValue("@DRUM_NUMBER", DRUM_NUMBER);
        
        cmd.Parameters.AddWithValue("@Solvant_PH", Solvant_PH);
        cmd.Parameters.AddWithValue("@Solvant_Temp", Solvant_Temp);
        cmd.Parameters.AddWithValue("@Sample_0_hr_PH", Sample_0_hr_PH);
        cmd.Parameters.AddWithValue("@Sample_0_hr_Temp", Sample_0_hr_Temp);
        cmd.Parameters.AddWithValue("@Sample_1_5_hr_PH", Sample_1_5_hr_PH);
        cmd.Parameters.AddWithValue("@Sample_1_5_hr_Temp", Sample_1_5_hr_Temp);
        
        cmd.Parameters.AddWithValue("@Remark", Remark);
        cmd.Parameters.AddWithValue("@IS_ACTIVE", true);
        cmd.Parameters.AddWithValue("@CREATED_BY", HttpContext.Current.User.Identity.Name);
        cmd.Parameters.AddWithValue("@CREATED_DATE", DateTime.Now);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }


    public bool update(List<tbl_qcsicomill_ph> list)
    {
        bool ret = false;
        List<MySqlCommand> cmds = new List<MySqlCommand>();
        string sql = @"Update tbl_qcsicomill_ph SET Work_Order_Number=@Work_Order_Number, DRUM_NUMBER=@DRUM_NUMBER, Solvant_PH=@Solvant_PH, Solvant_Temp=@Solvant_Temp, Sample_0_hr_PH=@Sample_0_hr_PH, Sample_0_hr_Temp=@Sample_0_hr_Temp, Sample_1_5_hr_PH=@Sample_1_5_hr_PH, Sample_1_5_hr_Temp=@Sample_1_5_hr_Temp, Remark=@Remark, IS_ACTIVE=@IS_ACTIVE, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE PH_ID=@PH_ID";

        foreach (tbl_qcsicomill_ph row in list)
        {
            MySqlCommand cmd = new MySqlCommand(sql);

            cmd.Parameters.AddWithValue("@PH_ID", row.PH_ID);
            cmd.Parameters.AddWithValue("@Work_Order_Number", row.Work_Order_Number);
            cmd.Parameters.AddWithValue("@DRUM_NUMBER", row.DRUM_NUMBER);
            
            cmd.Parameters.AddWithValue("@Solvant_PH", row.Solvant_PH);
            cmd.Parameters.AddWithValue("@Solvant_Temp", row.Solvant_Temp);
            cmd.Parameters.AddWithValue("@Sample_0_hr_PH", row.Sample_0_hr_PH);
            cmd.Parameters.AddWithValue("@Sample_0_hr_Temp", row.Sample_0_hr_Temp);
            cmd.Parameters.AddWithValue("@Sample_1_5_hr_PH", row.Sample_1_5_hr_PH);
            cmd.Parameters.AddWithValue("@Sample_1_5_hr_Temp", row.Sample_1_5_hr_Temp);
            
            cmd.Parameters.AddWithValue("@Remark", row.Remark);
            cmd.Parameters.AddWithValue("@IS_ACTIVE", true);
            cmd.Parameters.AddWithValue("@UPDATED_BY", HttpContext.Current.User.Identity.Name);
            cmd.Parameters.AddWithValue("@UPDATED_DATE", DateTime.Now);

            cmds.Add(cmd);
        }
        ret = DBUtil.ExecuteNonQuery(cmds);
        return ret;
    }



    public int update(String PH_ID, String Work_Order_Number, String DRUM_NUMBER, String Solvant_PH, String Solvant_Temp, String Sample_0_hr_PH, String Sample_0_hr_Temp, String Sample_1_5_hr_PH, String Sample_1_5_hr_Temp, String Remark, bool IS_ACTIVE, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_qcsicomill_ph SET Work_Order_Number=@Work_Order_Number, DRUM_NUMBER=@DRUM_NUMBER, Solvant_PH=@Solvant_PH, Solvant_Temp=@Solvant_Temp, Sample_0_hr_PH=@Sample_0_hr_PH, Sample_0_hr_Temp=@Sample_0_hr_Temp, Sample_1_5_hr_PH=@Sample_1_5_hr_PH, Sample_1_5_hr_Temp=@Sample_1_5_hr_Temp, Remark=@Remark, IS_ACTIVE=@IS_ACTIVE, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE PH_ID=@PH_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@PH_ID", PH_ID);
        cmd.Parameters.AddWithValue("@Work_Order_Number", Work_Order_Number);
        cmd.Parameters.AddWithValue("@DRUM_NUMBER", DRUM_NUMBER);
        
        cmd.Parameters.AddWithValue("@Solvant_PH", Solvant_PH);
        cmd.Parameters.AddWithValue("@Solvant_Temp", Solvant_Temp);
        cmd.Parameters.AddWithValue("@Sample_0_hr_PH", Sample_0_hr_PH);
        cmd.Parameters.AddWithValue("@Sample_0_hr_Temp", Sample_0_hr_Temp);
        cmd.Parameters.AddWithValue("@Sample_1_5_hr_PH", Sample_1_5_hr_PH);
        cmd.Parameters.AddWithValue("@Sample_1_5_hr_Temp", Sample_1_5_hr_Temp);
        
        cmd.Parameters.AddWithValue("@Remark", Remark);
        cmd.Parameters.AddWithValue("@IS_ACTIVE",true);
        cmd.Parameters.AddWithValue("@UPDATED_BY", HttpContext.Current.User.Identity.Name);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", DateTime.Now);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(String PH_ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_qcsicomill_ph WHERE PH_ID=@PH_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@PH_ID", PH_ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }


    public List<tbl_qcsicomill_ph> ddlWorkOrderForSSA()
    {
        List<tbl_qcsicomill_ph> list = new List<tbl_qcsicomill_ph>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct Work_Order_Number from tbl_qcsicomill_ph Order By Work_Order_Number";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_qcsicomill_ph()
            {

                Work_Order_Number = dr["Work_Order_Number"].ToString(),

            });
        }

        return list;
    }


    public List<tbl_qcsicomill_ph> ddlDrumNumber(string WorkOrderNumber)
    {
        List<tbl_qcsicomill_ph> list = new List<tbl_qcsicomill_ph>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct Drum_Number from tbl_qcsicomill_ph where Work_Order_Number=@Work_Order_Number and Drum_Number<>'' order by Drum_Number";
        cmd.Parameters.AddWithValue("@Work_Order_Number", WorkOrderNumber);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_qcsicomill_ph()
            {
                DRUM_NUMBER = dr["Drum_Number"].ToString()
            });
        }

        return list;
    }

}